﻿Imports BE.BE

Namespace DAL

    Public Class DAL_Familia

        Shared Function listarFamilias() As List(Of Familia_BE)
            Dim strcomm As String = "SELECT id_familia, nombre_familia, descripcion_familia FROM familia WHERE borrado=0"
            Dim Ds As DataSet
            Dim listaFamilias As New List(Of Familia_BE)
            Try
                Ds = Helper_DAL.ExecuteDataset(strcomm)
                If Ds.Tables(0).Rows.Count > 0 Then
                    For Each dr As DataRow In Ds.Tables(0).Rows
                        Dim fam As New Familia_BE
                        fam.Id = dr.Item(0)
                        fam.Nombre = dr.Item(1).ToString
                        fam.Descripcion = dr.Item(2).ToString
                        listaFamilias.Add(fam)
                    Next
                End If
            Catch ex As Exception
                Throw ex
            End Try
            Return listaFamilias
        End Function


        Shared Function listarPatentesDeLaFamilia(ByVal pId As Integer) As List(Of Patente_BE)
            Dim strcomm As String = "SELECT id_patente FROM familia_patente WHERE id_familia = " & pId
            Dim Ds As New DataSet
            Dim listapatentes As New List(Of Patente_BE)
            Ds = Helper_DAL.ExecuteDataset(strcomm)
            For Each dr As DataRow In Ds.Tables(0).Rows
                Dim patente As New Patente_BE
                patente = DAL_Patente.traerPatente(dr.Item(0))
                listapatentes.Add(patente)
            Next
            Return listapatentes
        End Function


        Shared Function listarFamiliasPorUsuario(ByVal pIdUsuario As Integer) As List(Of Familia_BE)
            Dim strcomm As String = ("SELECT id_usuario, uf.id_familia, nombre_familia, descripcion_familia " & _
                                    "FROM usuario_familia uf " & _
                                    "JOIN familia f " & _
                                    "ON uf.id_familia = f.id_familia " & _
                                    "WHERE uf.id_usuario = " & pIdUsuario & " AND f.borrado=0")
            Dim Ds As New DataSet
            Dim listaFamiliasPorUsuario As New List(Of Familia_BE)
            Try
                Ds = Helper_DAL.ExecuteDataset(strcomm)
                If Ds.Tables(0).Rows.Count > 0 Then
                    For Each dr As DataRow In Ds.Tables(0).Rows
                        Dim fam As New Familia_BE
                        fam.Id = dr.Item(1)
                        fam.Nombre = dr.Item(2).ToString
                        fam.Descripcion = dr.Item(3).ToString
                        listaFamiliasPorUsuario.Add(fam)
                    Next
                End If
            Catch ex As Exception
                Throw ex
            End Try
            Return listaFamiliasPorUsuario
        End Function


        Shared Sub nuevaFamilia(ByVal pFamilia As Familia_BE)
            Dim strcomm As String = "INSERT INTO familia (id_familia, nombre_familia, descripcion_familia, borrado) values " & _
                "(" & pFamilia.Id & ",'" & pFamilia.Nombre & "','" & pFamilia.Descripcion & "', 'false')"
            Try
                Helper_DAL.ExecuteNonQuery(strcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Shared Sub modificarFamilia(ByVal pFamilia As Familia_BE)
            Dim strcomm As String = "UPDATE familia SET nombre_familia='" & pFamilia.Nombre & "', descripcion_familia='" & pFamilia.Descripcion & "' WHERE id_familia=" & pFamilia.Id
            Try
                Helper_DAL.ExecuteNonQuery(strcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Shared Sub eliminarPatentesFamilia(ByVal pIdFamilia As Integer)
            Dim strcomm = "DELETE FROM familia_patente WHERE id_familia=" & pIdFamilia
            Try
                Helper_DAL.ExecuteNonQuery(strcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Shared Sub eliminarFamilia(ByVal pIdFamilia As Integer)
            Dim strcomm As String = "UPDATE familia SET borrado=1 WHERE id_familia=" & pIdFamilia
            Try
                Helper_DAL.ExecuteNonQuery(strcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Shared Sub eliminarFamiliasPorUsuario(ByVal pidusr As Integer)
            Dim strcomm As String = "DELETE FROM usuario_familia WHERE id_usuario= " & pidusr
            Try
                Helper_DAL.ExecuteNonQuery(strcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Shared Sub nuevaPatenteEnFamilia(ByVal pIdPatente As Integer, pIdfamilia As Integer, pDVH As Integer)
            Dim strcomm As String = "INSERT INTO familia_patente (id_familia, id_patente, dvh) VALUES " & _
                "(" & pIdfamilia & "," & pIdPatente & "," & pDVH & ")"
            Try
                Helper_DAL.ExecuteNonQuery(strcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub


        Shared Sub guardarFamiliaPorUsuario(ByVal pFamilia As Familia_BE, pId As Integer)
            Dim strcomm As String = "INSERT INTO usuario_familia (id_usuario, id_familia) VALUES " & _
                "(" & pId & "," & pFamilia.Id & ")"
            Try
                Helper_DAL.ExecuteNonQuery(strcomm)
            Catch ex As Exception
                Throw ex
            End Try
        End Sub

    End Class

End Namespace


